using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using NPOI;
using NPOI.XSSF;
using System.Reflection;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using System.IO;

namespace Daemon.Common
{
    public static class NPOIHelper
    {
        public static byte[] ExportExcel<T>(Dictionary<string, string> columnsHeader, List<T> dataSource, int defaultColumnWidth = 50, string title = null, string footer = null)
        {
            IWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("Sheet1");
            sheet.DefaultColumnWidth = defaultColumnWidth;

            IRow row;
            ICell cell;

            #region excel标题头
            int rowIndex = 0;
            if (!string.IsNullOrEmpty(title))
            {
                ICellStyle cellStyle = workbook.CreateCellStyle();
                cellStyle.VerticalAlignment = VerticalAlignment.Center;
                cellStyle.Alignment = HorizontalAlignment.Center;
                IFont font = workbook.CreateFont();
                font.FontHeightInPoints = 10;
                font.IsBold = true;
                cellStyle.SetFont(font);
                var region = new CellRangeAddress(1, 1, 0, columnsHeader.Keys.Count > 0 ? columnsHeader.Keys.Count - 1 : 0);
                sheet.AddMergedRegion(region);
                //合并单元格后样式
                ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region, BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index);

                row = sheet.CreateRow(rowIndex);
                cell = row.CreateCell(0);
                cell.SetCellValue(title);
                cell.CellStyle = cellStyle;
                rowIndex++;
            }
            #endregion

            #region 列头
            row = sheet.CreateRow(rowIndex);
            row.HeightInPoints = 20;
            int cellIndex = 0;
            foreach (var value in columnsHeader.Values)
            {
                ICellStyle cellStyle = workbook.CreateCellStyle();
                cellStyle = workbook.CreateCellStyle();
                cellStyle.BorderBottom = BorderStyle.Thin;
                cellStyle.BorderLeft = BorderStyle.Thin;
                cellStyle.BorderRight = BorderStyle.Thin;
                cellStyle.BorderTop = BorderStyle.Thin;
                //背景色
                cellStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index;
                cellStyle.FillPattern = FillPattern.SolidForeground;
                //水平垂直居中
                cellStyle.VerticalAlignment = VerticalAlignment.Center;
                cellStyle.Alignment = HorizontalAlignment.Center;
                IFont font = workbook.CreateFont();
                font.FontHeightInPoints = 10;
                font.IsBold = true;
                cellStyle.SetFont(font);

                cell = row.CreateCell(cellIndex);
                cell.CellStyle = cellStyle;
                cell.SetCellValue(value);
                cellIndex++;
            }
            rowIndex++;
            #endregion

            #region 主题内容

            //单元格样式 注：不要放循环里面，NPOI中调用workbook.CreateCellStyle()方法超过4000次会报错
            ICellStyle contentStyle = workbook.CreateCellStyle();
            contentStyle.BorderBottom = BorderStyle.Thin;
            contentStyle.BorderLeft = BorderStyle.Thin;
            contentStyle.BorderRight = BorderStyle.Thin;
            contentStyle.BorderTop = BorderStyle.Thin;
            contentStyle.VerticalAlignment = VerticalAlignment.Center;
            IFont contentFont = workbook.CreateFont();
            contentFont.FontHeightInPoints = 10;
            contentStyle.SetFont(contentFont);

            //日期格式样式
            ICellStyle dateStyle = workbook.CreateCellStyle();
            dateStyle.BorderBottom = BorderStyle.Thin;
            dateStyle.BorderLeft = BorderStyle.Thin;
            dateStyle.BorderRight = BorderStyle.Thin;
            dateStyle.BorderTop = BorderStyle.Thin;
            dateStyle.VerticalAlignment = VerticalAlignment.Center;
            dateStyle.SetFont(contentFont);
            IDataFormat format = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
            foreach (T item in dataSource)
            {
                row = sheet.CreateRow(rowIndex);
                row.HeightInPoints = 20;
                rowIndex++;
                Type type = item.GetType();
                PropertyInfo[] properties = type.GetProperties();
                if (properties.Length > 0)
                {
                    cellIndex = 0;
                    foreach (var key in columnsHeader.Keys)
                    {
                        cell = row.CreateCell(cellIndex);
                        cell.CellStyle = contentStyle;

                        if (properties.Select(x => x.Name.ToLower()).Contains(key.ToLower()))
                        {
                            var property = properties.Where(x => x.Name.ToLower() == key.ToLower()).FirstOrDefault();
                            string drValue = property.GetValue(item) == null ? "" : property.GetValue(item).ToString();
                            //当类型类似DateTime?时
                            var fullType = property.PropertyType.Name == "Nullable`1" ? property.PropertyType.GetGenericArguments()[0].FullName : property.PropertyType.FullName;
                            switch (fullType)
                            {
                                case "System.String": //字符串类型
                                    cell.SetCellValue(drValue);
                                    break;
                                case "System.DateTime": //日期类型
                                    if (string.IsNullOrEmpty(drValue) || drValue == "0001/1/1 0:00:00")
                                    {
                                        cell.SetCellValue("");
                                    }
                                    else
                                    {
                                        DateTime dateV;
                                        DateTime.TryParse(drValue, out dateV);
                                        cell.SetCellValue(dateV);

                                        cell.CellStyle = dateStyle; //格式化显示
                                    }
                                    break;
                                case "System.Boolean": //布尔型
                                    bool boolV = false;
                                    bool.TryParse(drValue, out boolV);
                                    cell.SetCellValue(boolV);
                                    break;
                                case "System.Int16": //整型
                                case "System.Int32":
                                case "System.Int64":
                                case "System.Byte":
                                    int intV = 0;
                                    int.TryParse(drValue, out intV);
                                    cell.SetCellValue(intV);
                                    break;
                                case "System.Decimal": //浮点型
                                case "System.Double":
                                    double doubV = 0;
                                    double.TryParse(drValue, out doubV);
                                    cell.SetCellValue(doubV);
                                    break;
                                case "System.DBNull": //空值处理
                                    cell.SetCellValue("");
                                    break;
                                default:
                                    cell.SetCellValue("");
                                    break;
                            }
                        }
                        cellIndex++;
                    }
                }

            }
            #endregion

            #region 结尾行
            if (!string.IsNullOrEmpty(footer))
            {
                ICellStyle cellStyle = workbook.CreateCellStyle();
                cellStyle.VerticalAlignment = VerticalAlignment.Center;
                cellStyle.Alignment = HorizontalAlignment.Center;
                IFont font = workbook.CreateFont();
                font.FontHeightInPoints = 10;
                font.IsBold = true;
                cellStyle.SetFont(font);
                var region = new CellRangeAddress(rowIndex, rowIndex, 0, columnsHeader.Keys.Count > 0 ? columnsHeader.Keys.Count - 1 : 0);
                sheet.AddMergedRegion(region);
                //合并单元格后样式
                ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region, BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index);

                row = sheet.CreateRow(rowIndex);
                row.HeightInPoints = 20;
                cell = row.CreateCell(0);
                cell.SetCellValue(footer);
                cell.CellStyle = cellStyle;
            }
            #endregion

            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms, false);
                ms.Flush();
                ms.Seek(ms.Length, SeekOrigin.Begin);
                return ms.ToArray();
            }
        }

        public static Dictionary<string, List<List<string>>> GetExcelData(IWorkbook wb, Func<string, bool> IsSkipSheet = null)
        {
            Dictionary<string, List<List<string>>> result = null;
            if (wb != null)
            {
                result = new Dictionary<string, List<List<string>>>();
                for (int i = 0; i < wb.NumberOfSheets; i++)
                {
                    ISheet sheet = wb.GetSheetAt(i);
                    result.Add(sheet.SheetName, GetSheetData(sheet));
                }
            }
            return result;
        }

        public static HSSFWorkbook ConverXSSFToHSSF(XSSFWorkbook source)
        {
            HSSFWorkbook retVal = new HSSFWorkbook();
            for (int i = 0; i < source?.NumberOfSheets; i++)
            {
                HSSFSheet hssfSheet = (HSSFSheet)retVal.CreateSheet(source?.GetSheetAt(i).SheetName);

                XSSFSheet xssfsheet = (XSSFSheet)source?.GetSheetAt(i);
                CopySheets(xssfsheet, hssfSheet, retVal);
            }

            return retVal;
        }

        public static List<List<string>> GetSheetData(ISheet sheet)
        {
            List<List<string>> sheetList = null;
            if (sheet != null)
            {
                sheetList = new List<List<string>>();

                int startRow = 0;//sheet.FirstRowNum
                int endRow = sheet.LastRowNum;
                List<string> rowList;
                IRow row;
                IRow FirstRow = sheet.GetRow(0);

                for (int i = startRow; i <= endRow; i++)
                {
                    row = sheet.GetRow(i);
                    rowList = GetRowData(row);
                    if (rowList == null)
                    {
                        sheetList.Add(new List<string>());
                    }
                    else
                    {
                        sheetList.Add(rowList);
                    }
                }
            }
            return sheetList;
        }

        public static List<string> GetRowData(IRow row)
        {
            List<string> result = null;
            if (row != null)
            {
                result = new List<string>();
                int startColumn = 0;
                int endColumn = row.LastCellNum;
                for (int i = startColumn; i <= endColumn; i++)
                {
                    result.Add(GetCellString(row.GetCell(i)));
                }
            }
            return result;
        }

        public static string GetCellString(ICell cell)
        {
            if (cell != null)
            {
                switch (cell.CellType)
                {
                    case CellType.Unknown:
                        return "";
                    case CellType.Numeric:
                        return cell.NumericCellValue.ToString();
                    case CellType.String:
                        return cell.StringCellValue;
                    case CellType.Formula:
                        return cell.CellFormula;
                    case CellType.Blank:
                        return "";
                    case CellType.Boolean:
                        return cell.BooleanCellValue.ToString();
                    case CellType.Error:
                        return "";
                    default:
                        return "";
                }
            }
            else
            {
                return "";
            }
        }

        private static void CopySheets(XSSFSheet source, HSSFSheet destination, HSSFWorkbook retVal)
        {
            int maxColumnNum = 0;
            Dictionary<int, XSSFCellStyle> styleMap = new Dictionary<int, XSSFCellStyle>();
            for (int i = source.FirstRowNum; i <= source.LastRowNum; i++)
            {
                XSSFRow srcRow = (XSSFRow)source.GetRow(i);
                HSSFRow destRow = (HSSFRow)destination.CreateRow(i);
                if (srcRow != null)
                {
                    CopyRow(source, destination, srcRow, destRow, styleMap, retVal);
                    if (srcRow.LastCellNum > maxColumnNum)
                    {
                        maxColumnNum = srcRow.LastCellNum;
                    }
                }
            }

            for (int i = 0; i <= maxColumnNum; i++)
            {
                destination.SetColumnWidth(i, source.GetColumnWidth(i));
            }
        }

        private static void CopyRow(XSSFSheet srcSheet, HSSFSheet destSheet, XSSFRow srcRow, HSSFRow destRow, Dictionary<int, XSSFCellStyle> styleMap, HSSFWorkbook retVal)
        {
            // manage a list of merged zone in order to not insert two times a
            // merged zone
            List<CellRangeAddress> mergedRegions = new List<CellRangeAddress>();
            destRow.Height = srcRow.Height;

            // pour chaque row
            for (int j = srcRow.FirstCellNum; j <= srcRow.LastCellNum; j++)
            {
                XSSFCell oldCell = (XSSFCell)srcRow.GetCell(j); // ancienne cell
                HSSFCell newCell = (HSSFCell)destRow.GetCell(j); // new cell
                if (oldCell != null)
                {
                    if (newCell == null)
                    {
                        newCell = (HSSFCell)destRow.CreateCell(j);
                    }

                    // copy cell
                    CopyCell(oldCell, newCell, styleMap);

                    // copy les information de fusion entre les cellules
                    CellRangeAddress mergedRegion = GetMergedRegion(srcSheet, srcRow.RowNum, (short)oldCell.ColumnIndex);

                    if (mergedRegion != null)
                    {
                        CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.FirstRow, mergedRegion.LastRow, mergedRegion.FirstColumn, mergedRegion.LastColumn);
                        if (!mergedRegions.Contains(newMergedRegion))
                        {
                            mergedRegions.Add(newMergedRegion);
                            destSheet.AddMergedRegion(newMergedRegion);
                        }

                        if (newMergedRegion.FirstColumn == 0 && newMergedRegion.LastColumn == 6 && newMergedRegion.FirstRow == newMergedRegion.LastRow)
                        {
                            HSSFCellStyle style2 = (HSSFCellStyle)retVal.CreateCellStyle();
                            style2.VerticalAlignment = VerticalAlignment.Center;
                            style2.Alignment = HorizontalAlignment.Left;
                            style2.FillForegroundColor = HSSFColor.Teal.Index;
                            style2.FillPattern = FillPattern.SolidForeground;

                            for (int i = destRow.FirstCellNum; i <= destRow.LastCellNum; i++)
                            {
                                if (destRow.GetCell(i) != null)
                                {
                                    destRow.GetCell(i).CellStyle = style2;
                                }
                            }
                        }
                    }
                }
            }
        }

        private static void CopyCell(XSSFCell oldCell, HSSFCell newCell, Dictionary<int, XSSFCellStyle> styleMap)
        {
            if (styleMap != null)
            {
                int hashCode = oldCell.CellStyle.Index;
                XSSFCellStyle sourceCellStyle = null;
                if (styleMap.TryGetValue(hashCode, out sourceCellStyle))
                {
                }

                HSSFCellStyle destnCellStyle = (HSSFCellStyle)newCell.CellStyle;
                if (sourceCellStyle == null)
                {
                    sourceCellStyle = (XSSFCellStyle)oldCell.Sheet.Workbook.CreateCellStyle();
                }

                // destnCellStyle.CloneStyleFrom(oldCell.CellStyle);
                if (!styleMap.Any(p => p.Key == hashCode))
                {
                    styleMap.Add(hashCode, sourceCellStyle);
                }

                destnCellStyle.VerticalAlignment = VerticalAlignment.Top;
                newCell.CellStyle = (HSSFCellStyle)destnCellStyle;
            }

            switch (oldCell.CellType)
            {
                case CellType.String:
                    newCell.SetCellValue(oldCell.StringCellValue);
                    break;
                case CellType.Numeric:
                    newCell.SetCellValue(oldCell.NumericCellValue);
                    break;
                case CellType.Blank:
                    newCell.SetCellType(CellType.Blank);
                    break;
                case CellType.Boolean:
                    newCell.SetCellValue(oldCell.BooleanCellValue);
                    break;
                case CellType.Error:
                    newCell.SetCellErrorValue(FormulaError.ForInt(oldCell.ErrorCellValue));
                    break;
                case CellType.Formula:
                    newCell.SetCellFormula(oldCell.CellFormula);
                    break;
                default:
                    break;
            }
        }

        private static CellRangeAddress GetMergedRegion(XSSFSheet sheet, int rowNum, short cellNum)
        {
            for (int i = 0; i < sheet.NumMergedRegions; i++)
            {
                CellRangeAddress merged = sheet.GetMergedRegion(i);
                if (merged.IsInRange(rowNum, cellNum))
                {
                    return merged;
                }
            }

            return null;
        }
    }
}